﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using CinemaBookingProject.DBLayer;
using System.Data;
using System.Threading;
using System.Configuration;

public class DBBooking
{

    
    private static object obj = new object();
    private static string connString = ConfigurationManager.ConnectionStrings["Balder"].ConnectionString;
    private static SqlConnection Connection = new SqlConnection(connString);
    
    //private static string connString = "Data Source=balder.ucn.dk;Initial Catalog=dmaa0912_E8;Persist Security Info=True;User ID=dmaa0912_E8;Password=MaaGodt";
    


    DBTicket dbt = new DBTicket();
    /// <summary>
    /// Returns a booking object from the database
    /// </summary>
    /// <param name="id">The id of the booking object</param>
    /// <returns>Booking object</returns>
    public Booking Read(int id)
    {
            SqlDataReader rdr = null;
            Booking booking = null;
            List<Ticket> tic = null;
      
            string readQuery = "Select * from Booking where ID = @id;";
            try
            {
                Connection.Open();

                SqlCommand cmd = new SqlCommand(readQuery, Connection);
                cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;

                rdr = cmd.ExecuteReader();
                
                while (rdr.Read())
                {
                    //tic = dbt.ReadFromBookingId((int)rdr["ID"]);

                    booking = new Booking((int)rdr["ID"], (double)rdr["TotalPrice"], (byte)rdr["BookingStatus"], (string)rdr["CustomerPhone"]);

                }
            }
            finally
            {
                //close reader
                if (rdr != null)
                {
                    rdr.Close();
                }

                // close connection
                if (Connection != null)
                {
                    Connection.Close();
                }
            }

            return booking;
        }
    public Booking Read()
    {
        SqlDataReader rdr = null;
        Booking booking = null;
        List<Ticket> tic = null;

        string readQuery = "Select * from Booking;";
        try
        {
            Connection.Open();

            SqlCommand cmd = new SqlCommand(readQuery, Connection);
            

            rdr = cmd.ExecuteReader();

            while (rdr.Read())
            {
                //tic = dbt.ReadFromBookingId((int)rdr["ID"]);

                booking = new Booking((int)rdr["ID"], (double)rdr["TotalPrice"], (byte)rdr["BookingStatus"], (string)rdr["CustomerPhone"]);

            }
        }
        finally
        {
            //close reader
            if (rdr != null)
            {
                rdr.Close();
            }

            // close connection
            if (Connection != null)
            {
                Connection.Close();
            }
        }

        return booking;
    }

    /// <summary>
    /// Returns a list of booking objects from the database
    /// </summary>
    /// <param name="customer">The Customer object</param>
    /// <returns>List of Booking object</returns>
    public List<Booking> Read(Customer customer)
    {
        SqlDataReader rdr = null;
        List<Booking> booking = new List<Booking>();
        List<Ticket> tic = null;
        string readQuery = "Select * from Booking where CustomerPhone LIKE @customerPhone;";
        try
        {
            Connection.Open();
            
            SqlCommand cmd = new SqlCommand(readQuery, Connection);
            cmd.Parameters.Add("@customerPhone", SqlDbType.VarChar).Value = "%" + customer.PhoneNo + "%";

            rdr = cmd.ExecuteReader();
            
            while (rdr.Read())
            {
                
               booking.Add(new Booking((int)rdr["ID"], (double)rdr["TotalPrice"], (byte)rdr["BookingStatus"], (string)rdr["CustomerPhone"]));
            }
        }
        finally
        {
            //close reader
            if (rdr != null)
            {
                rdr.Close();
            }

            // close connection
            if (Connection != null)
            {
                Connection.Close();
            }
        }

        return booking;
    }
    /// <summary>
    /// Inserts a booking object into the database
    /// </summary>
    /// <param name="customer">The Customer object</param>
    /// <returns>+1 or -1</returns>
    public int Insert(Booking booking)
    {
        int id = 0;

        string query = "INSERT INTO Booking(ID, BookingStatus, TotalPrice, CustomerPhone)VALUES(@ID, @bookingstatus, @totalprice, @customerphone);";

        try
        {
            Monitor.Enter(obj);

            id = BookingMaxID();
            booking.TotalPrice = CollectTicketPrice(id);
            SqlCommand cmd = new SqlCommand(query, Connection);

            cmd.Parameters.Add("@ID", SqlDbType.Int).Value = id;
            cmd.Parameters.Add("@bookingstatus", SqlDbType.VarChar).Value = booking.Status;
            cmd.Parameters.Add("@totalprice", SqlDbType.Float).Value = booking.TotalPrice;
            cmd.Parameters.Add("@customerphone", SqlDbType.VarChar).Value = booking.CustomerPhone;

            Connection.Open();
            //in case we can't add the booking, there's no point in returning the actual id.
            if (cmd.ExecuteNonQuery() < 1)
            {
                id = 0;
            }
        }
        //catch (SqlException)
        //{
        //    success = -1;
        //}
        catch (Exception)
        {

            throw;
        }
        finally
        {
            Connection.Close();
            Monitor.Exit(obj);
        }

        return id;
    }
    /// <summary>
    /// updates a booking object in the database
    /// </summary>
    /// <param name="customer">The Customer object</param>
    /// <returns>+1 or -1</returns>
    public int Update(Booking booking)
    {
        int succeed = 0;

        string query = "UPDATE Booking SET BookingStatus = @bookingstatus, TotalPrice = @totalprice Where CustomerPhone = @customerphone;";
        try
        {
            
            SqlCommand cmd = new SqlCommand(query, Connection);
            booking.TotalPrice = CollectTicketPrice(booking.Id);
            cmd.Parameters.Add("@customerphone", SqlDbType.VarChar).Value = booking.CustomerPhone;
            cmd.Parameters.Add("@bookingstatus", SqlDbType.VarChar).Value = booking.Status;
            cmd.Parameters.Add("@totalprice", SqlDbType.Float).Value = booking.TotalPrice;

            Monitor.Enter(obj);
            Connection.Open();
            succeed = cmd.ExecuteNonQuery();
        }
         //catch (SqlException)  
         //{
         //    succeed = -1;
         //}
         catch (Exception)
         {
             
             throw;
         }
        finally
         {
             Connection.Close();
             Monitor.Exit(obj);
         }
         return succeed;
    }
    /// <summary>
    /// Returns the highest id number from the database and adds 1
    /// </summary>
    /// <returns>+1 or -1</returns>
    public int BookingMaxID()
    {
        int maxID = 0;

        SqlDataReader rdr = null;
        string query = "SELECT MAX(ID) AS ID FROM BOOKING;";
        try
        {
            Connection.Open();
            
            SqlCommand cmd = new SqlCommand(query, Connection);


            rdr = cmd.ExecuteReader();

            while (rdr.Read())
            {
                maxID = (int)rdr["ID"];
            }
        }
        finally
        {
            //close reader
            if (rdr != null)
            {
                rdr.Close();
            }

            // close connection
            if (Connection != null)
            {
                Connection.Close();
            }
        }
        return maxID + 1;
    }

    public double CollectTicketPrice(int id)
    {
        double sum = 0;
        List<Ticket> tickets = dbt.ReadFromBookingId(id);

        foreach (Ticket obj in tickets)
        {
            sum += obj.TicketPrice;
        }
        return sum;
    }

    public List<Booking> FindBookingByTime()
    {
        SqlDataReader rdr = null;
       
        List<Booking> bookingsByTime = new List<Booking>();

        string readQuery = "select booking.* from showtime join ticket on Ticket.ShowTimeID = showtime.id join booking on booking.id = Ticket.BookingID where (showtime.shtime BETWEEN  CONVERT (time, CURRENT_TIMESTAMP) and CONVERT(time, DATEADD(MINUTE, 30, CURRENT_TIMESTAMP)) and BookingStatus = 2)";
        try
        {

            Connection.Open();

            SqlCommand cmd = new SqlCommand(readQuery, Connection);


            rdr = cmd.ExecuteReader();

            while (rdr.Read())
            {
               
                //tic = dbt.ReadFromBookingId((int)rdr["ID"]);
               
                bookingsByTime.Add(new Booking((int)rdr["ID"], (double)rdr["TotalPrice"], (byte)rdr["BookingStatus"], (string)rdr["CustomerPhone"]));

            }
        }
        finally
        {
            //close reader
            if (rdr != null)
            {
                rdr.Close();
            }

            // close connection
            if (Connection != null)
            {
                Connection.Close();
            }
            
        }
        return bookingsByTime;
    }


    public void Delete(int id)
    {

        string query = "DELETE FROM Booking WHERE ID = @Id";
        try
        {

            SqlCommand cmd = new SqlCommand(query, Connection);
            cmd.Parameters.Add("@Id", SqlDbType.Int).Value = id;

            Monitor.Enter(obj);
            Connection.Open();
            cmd.ExecuteNonQuery();
        }
        catch (Exception)
        {

            throw;
        }
        finally
        {
            Connection.Close();
            Monitor.Exit(obj);
        }
    }
}

